OpenEdge Development:<br />Programming Interfaces Generating reports with control breaks and aggregates

You can sort data using the BY option of the record phrase. For example, this block header in a report procedure sorts the Customer records by the Sales-rep code:

FOR EACH Customer BY Sales-Rep:

A control break separates sorted data into meaningful groups. Breaking sorted data by Sales-rep separates each sales rep’s customers into a separate break group. Break groups allow you to calculate summary data on subsets of data. For example, you could total the number of customers that each sales rep serves.

The BREAK option of the record phrase sets up your data for control breaks, as shown in this block header statement:

FOR EACH Customer BREAK BY Sales-Rep:

Once you’ve set up the control break, you can use the aggregate phrase on an output statement item to calculate summary information. The diagram below shows the syntax of the aggregate phrase:

Syntax
[ `aggregate-option` ] ... [ BY `break-group` ] ...

Table 7–1: Aggregate phrase options
Aggregate option	Description
AVERAGE	Calculates the average of all the expression’s values, and calculates the overall average for the break groups.
SUB-AVERAGE	Calculates the average of all the expression’s values, but does not calculate the overall average.
COUNT	Counts the number of items in the break group, and calculates the overall count of the break groups.
SUB-COUNT	Counts the number of items in the break group, but does not calculate the overall count.
MAXIMUM	Finds the maximum value of the expression in the break group, and finds the overall maximum of the break groups.
SUB-MAXIMUM	Finds the maximum value of the expression in the break group, but does not find the overall maximum.
MINIMUM	Finds the minimum value of the expression in the break group, and finds the overall minimum of the break groups.
SUB-MINIMUM	Finds the minimum value of the expression in the break group, but does not find the overall minimum.
TOTAL	Calculates the total of all the expression’s values, and calculates the overall total for the break groups.
SUB-TOTAL	Calculates the total of all the expression’s values, but does not calculate the overall total for the break groups.

For example, in the code below, Sales-rep is the control break, Balance is the expression, and the aggregate phrase with the TOTAL option appears in parenthesis immediately after the expression in the DISPLAY statement:

FOR EACH Customer FIELDS (Balance Sales-Rep) BREAK BY Sales-rep: DISPLAY Customer Balance (TOTAL BY Sales-Rep). END.

The TOTAL aggregate option totals the balances for each break group. Each time the value of Sales-rep changes in the sorted data, Progress creates another break group, and the TOTAL BY aggregate function calculates and outputs the total of customer balances for that sales rep’s customers. Finally, at the end of the report, the TOTAL option calculates and outputs the total balances of all the break groups.

Open i-10-05.p and run it.

Choose Report. The Report Output dialog box appears as shown in the following screen:

The members of each break group all have the same Sales-rep value. Notice that the report separates each break group with white space and outputs the result of the aggregate phrase options immediately following the break groups.

If you scroll to the bottom of the report, you can see the aggregate values for all the break groups.

Choose OK, then Exit, and then press SPACEBAR to return to the Procedure Editor.

i-10-05.p
/* {i-10-in.i} Common Interface Setup Code / /******* DEFINE TRIGGERS *******/ ON CHOOSE of b-rep DO: OUTPUT TO "tut-temp.txt". /1/ FOR EACH Customer FIELDS (Sales-rep Name Balance) BREAK BY Sales-rep WITH STREAM-IO: DISPLAY Sales-rep Name /2/ Balance (COUNT TOTAL AVERAGE BY Sales-rep). END. OUTPUT CLOSE. ASSIGN Rep-Editor:READ-ONLY IN FRAME dialog1 = YES Rep-Editor:SENSITIVE IN FRAME Dialog1 = YES FRAME dialog1:TITLE = "Report Output" Stat = Rep-Editor:READ-FILE("tut-temp.txt") IN FRAME Dialog1. IF Stat THEN DO: ENABLE Rep-Editor b-ok WITH FRAME Dialog1. WAIT-FOR GO OF FRAME Dialog1. HIDE FRAME Dialog1. END. END. /******* MAIN LOGIC ********/ ENABLE ALL WITH FRAME Frame1. WAIT-FOR CHOOSE OF b-exit.

i-10-05.p

 /* {i-10-in.i} Common Interface Setup Code */  
     
    /**********  DEFINE TRIGGERS  **********/
    ON CHOOSE of b-rep
    DO:  
        OUTPUT TO "tut-temp.txt".
/*1*/    FOR EACH Customer FIELDS (Sales-rep Name Balance) BREAK BY Sales-rep 
           WITH STREAM-IO:
            DISPLAY Sales-rep Name 
/*2*/           Balance (COUNT TOTAL AVERAGE BY Sales-rep).
        END.
        OUTPUT CLOSE.
    
        ASSIGN Rep-Editor:READ-ONLY IN FRAME dialog1 = YES
             Rep-Editor:SENSITIVE IN FRAME Dialog1 = YES 
             FRAME dialog1:TITLE = "Report Output"
             Stat = Rep-Editor:READ-FILE("tut-temp.txt") IN FRAME Dialog1.
                
        IF Stat THEN DO:
            ENABLE Rep-Editor b-ok WITH FRAME Dialog1.
            WAIT-FOR GO OF FRAME Dialog1.
            HIDE FRAME Dialog1.
        END.
    END.

    /**********  MAIN LOGIC  **********/
    ENABLE ALL WITH FRAME Frame1.
    WAIT-FOR CHOOSE OF b-exit.